import pymysql
# 数据库中插入数据表格数据
def insert_dataForm_mysql(date_time,Person_in_charge,Namee,Wechat_number,Original_ID,Fan_source,Typee,Sex,Sex_rate,Accumulative_attention,Newly_added,Abolish_concern,Net_growth,Headline_readings,Comprehensive_reading_population,Comprehensive_reading_frequency,Conversational_reading,Conversational_reading_frequency,friends_reading_number,Friends_circle_reading_frequency,Forwarding_number,Number_of_forwarding,Collection_number,Collection_times,Historical_news_readings,Real_reading,Headline_reading_rate,Comprehensive_reading_rate,True_reading_rate,Two_transmission_ratio,
                          Per_capita_opening_times,Forwarding_rate,Opening_rate,Exposure,Clicks,Total_income,The_exposure_rate,Clicking_rate,Exposure_unit_price,Click_unit_price,Reading_unit_price,Vermicelli_unit_price,Main_company):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_insert = "INSERT INTO Data_form_table(时间,负责人,名称,微信号,原始ID,粉丝来源,类型,性别,男女率,累计关注,新增,取关,净增,头条阅读数,综合阅读人数,综合阅读次数,会话阅读人数,会话阅读次数,朋友圈阅读人数,朋友圈阅读次数,转发人数,转发次数,收藏人数,收藏次数,历史消息阅读次数,真实阅读,头条阅读率,综合阅读率,真实阅读率,二次传播比,人均打开次数,转发率,打开率,曝光量,点击量,总收入,曝光率,点击率,曝光单价,点击单价,阅读单价,粉丝单价,主体公司) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}','{41}','{42}')".format(
        date_time,Person_in_charge,Namee,Wechat_number,Original_ID,Fan_source,Typee,Sex,Sex_rate,Accumulative_attention,Newly_added,Abolish_concern,Net_growth,Headline_readings,Comprehensive_reading_population,Comprehensive_reading_frequency,Conversational_reading,Conversational_reading_frequency,friends_reading_number,Friends_circle_reading_frequency,Forwarding_number,Number_of_forwarding,Collection_number,Collection_times,Historical_news_readings,Real_reading,Headline_reading_rate,Comprehensive_reading_rate,True_reading_rate,Two_transmission_ratio,Per_capita_opening_times,Forwarding_rate,Opening_rate,Exposure,Clicks,Total_income,The_exposure_rate,Clicking_rate,Exposure_unit_price,Click_unit_price,Reading_unit_price,Vermicelli_unit_price,Main_company)
    # 执行语句
    cursor.execute(sql_insert)
    #事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()

def insert_violation_data_table_mysql(date_time,Person_in_charge,Namee,Wechat_number,Original_ID,Fan_source,typee,Sex,Accumulative_attention,Type_of_violation,Irregularities,Violation_treatment,Whether_or_not_to_ban,Main_company):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_insert = "INSERT INTO Violation_data_table(时间,负责人, 名称, 微信号, 原始ID, 粉丝来源, 类型, 性别, 累计关注,违规类型, 违规内容, 违规处理, 是否封禁, 主体公司)VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')".format(date_time,Person_in_charge,Namee,Wechat_number,Original_ID,Fan_source,typee,Sex,Accumulative_attention,Type_of_violation,Irregularities,Violation_treatment,Whether_or_not_to_ban,Main_company)
    # 执行语句
    cursor.execute(sql_insert)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()


def insert_Flow_master_data_table(date_time, Main_company, Person_in_charge, Namee, Wechat_number,Original_ID, Fan_source, typee, Sex, Account_income):
    conn = pymysql.Connect(
            host='',  # mysql服务器地址
            port=3306,  # mysql服务器端口号
            user='root',  # 用户名
            password='',  # 密码
            db='WeChat_backstage_data',  # 数据库名
            charset='utf8'  ##连接编码
        )
    cursor = conn.cursor()
    sql_insert_mysql = "INSERT INTO Flow_master_data_table(时间, 主体公司, 负责人, 名称, 微信号, 原始ID, 粉丝来源, 类型, 性别, 账户收入)VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')".format(
        date_time, Main_company, Person_in_charge, Namee, Wechat_number, Original_ID, Fan_source, typee, Sex, Account_income)
    # 执行语句
    cursor.execute(sql_insert_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()


def insert_Advertiser_data_table(date_time,Person_in_charge,Namee,Wechat_number,Original_ID,Fan_source,typee,Sex,Accumulative_attention,Service_provider,Amount_of_money,Main_company):
    conn = pymysql.Connect(
                host='',  # mysql服务器地址
                port=3306,  # mysql服务器端口号
                user='root',  # 用户名
                password='',  # 密码
                db='WeChat_backstage_data',  # 数据库名
                charset='utf8'  ##连接编码
            )
    cursor = conn.cursor()
    sql_insert_mysql = "INSERT INTO Advertiser_data_table(时间, 负责人, 名称, 微信号, 原始ID, 粉丝来源, 类型, 性别, 累计关注,服务商, 金额, 主体公司)VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')".format(
        date_time, Person_in_charge,Namee,Wechat_number,Original_ID,Fan_source,typee,Sex,Accumulative_attention,Service_provider,Amount_of_money,Main_company)
    # 执行语句
    cursor.execute(sql_insert_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()

def insert_reading_comparison_12_hour_table(date_time , Main_company , Person_in_charge , Namee , Wechat_number , Original_ID , Fan_source , typee , Sex , Accumulative_attention , Picture_text_transmission_time , first_headlines , first_Reading , sec_headlines , sec_Reading ,third_headlines , third_Reading , four_headlines , four_Reading , five_headlines , five_Reading , six_headlines , six_Reading , seven_headlines , seven_Reading , eight_headlines , eight_Reading ):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_insert_mysql = "INSERT INTO reading_comparison_12_hour(时间 ,主体公司,负责人,	名称,微信号,	原始ID,	粉丝来源	,类型,性别,	累计关注,图文发送时间,1条标题	,1条阅读	,2条标题	,2条阅读	,3条标题	,3条阅读	,4条标题	,4条阅读	,5条标题	,5条阅读	,6条标题,6条阅读	,7条标题	,7条阅读,8条标题,8条阅读)VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}')".format(
        date_time , Main_company , Person_in_charge , Namee , Wechat_number , Original_ID , Fan_source , typee , Sex , Accumulative_attention , Picture_text_transmission_time , first_headlines , first_Reading , sec_headlines , sec_Reading ,third_headlines , third_Reading , four_headlines , four_Reading , five_headlines , five_Reading , six_headlines , six_Reading , seven_headlines , seven_Reading , eight_headlines , eight_Reading)
    # 执行语句
    cursor.execute(sql_insert_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()

def insert_reading_comparison_36_hour_table(date_time , Main_company , Person_in_charge , Namee , Wechat_number , Original_ID , Fan_source , typee , Sex , Accumulative_attention , Picture_text_transmission_time , first_headlines , first_Reading , sec_headlines , sec_Reading ,third_headlines , third_Reading , four_headlines , four_Reading , five_headlines , five_Reading , six_headlines , six_Reading , seven_headlines , seven_Reading , eight_headlines , eight_Reading ):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_insert_mysql = "INSERT INTO reading_comparison_36_hour(时间 ,主体公司,负责人,	名称,微信号,	原始ID,	粉丝来源	,类型,性别,	累计关注,图文发送时间,1条标题	,1条阅读	,2条标题	,2条阅读	,3条标题	,3条阅读	,4条标题	,4条阅读	,5条标题	,5条阅读	,6条标题,6条阅读	,7条标题	,7条阅读,8条标题,8条阅读)VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}')".format(
        date_time , Main_company , Person_in_charge , Namee , Wechat_number , Original_ID , Fan_source , typee , Sex , Accumulative_attention , Picture_text_transmission_time , first_headlines , first_Reading , sec_headlines , sec_Reading ,third_headlines , third_Reading , four_headlines , four_Reading , five_headlines , five_Reading , six_headlines , six_Reading , seven_headlines , seven_Reading , eight_headlines , eight_Reading)
    # 执行语句
    cursor.execute(sql_insert_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()

def select_violation_data_table_mysql(date_time,Original_ID):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_select_mysql = "SELECT * FROM Violation_data_table WHERE 时间='{0}' AND 原始ID = '{1}'".format(date_time, Original_ID)
    # 执行语句
    effect_row = cursor.execute(sql_select_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return effect_row


def select_Data_form_table(date_time,Original_ID):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_select_mysql = "SELECT * FROM Data_form_table WHERE 时间='{0}' AND 原始ID = '{1}'".format(date_time,Original_ID)
    # 执行语句
    effect_row = cursor.execute(sql_select_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return effect_row

def select_Advertiser_data_table(date_time,Original_ID):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_select_mysql = "SELECT * FROM Advertiser_data_table WHERE 时间='{0}' AND 原始ID = '{1}'".format(date_time, Original_ID)
    # 执行语句
    effect_row = cursor.execute(sql_select_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return effect_row

def select_Flow_master_data_table(date_time,Original_ID):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_select_mysql = "SELECT * FROM Flow_master_data_table WHERE 时间='{0}' AND 原始ID = '{1}'".format(date_time, Original_ID)
    # 执行语句
    effect_row = cursor.execute(sql_select_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return effect_row

def select_reading_comparison_12_hour(date_time,Original_ID):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_select_mysql = "SELECT * FROM reading_comparison_12_hour WHERE 时间='{0}' AND 原始ID = '{1}'".format(date_time, Original_ID)
    # 执行语句
    effect_row = cursor.execute(sql_select_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return effect_row

def select_reading_comparison_36_hour(date_time,Original_ID):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='',  # 密码
        db='WeChat_backstage_data',  # 数据库名
        charset='utf8'  ##连接编码
    )
    cursor = conn.cursor()
    sql_select_mysql = "SELECT * FROM reading_comparison_36_hour WHERE 时间='{0}' AND 原始ID = '{1}'".format(date_time, Original_ID)
    # 执行语句
    effect_row = cursor.execute(sql_select_mysql)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()
    return effect_row